﻿<cfsilent>
	<cfscript>

		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		propAdvice = getProperty("serviceFactory").getBean("studentPropertyAdvice");
		
		sessionAdvice = getProperty("serviceFactory").getBean("sessionAdvice");
		depId = sessionAdvice.getUserProp("teacherDepartment");

		subjectId = event.getArg('SubID');
		grade = event.getArg('Grade');
		
		sql = "SELECT a.sbj_name,a.sbj_short 
				FROM t_subject a 
				WHERE a.sbj_id = :subjectId ";
				
		queryObj = new Query( datasource=application.dnsSlave ) ;
   		queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
		
		rs_subject= queryObj.execute( sql=sql ).getResult();
		
		sql = "SELECT 
					  c.sbj_name, b.grade, 
					  b.cls_name, a.stu_id, 
					  a.stu_name, a.stu_sex, 
					  t_nation.nation_name, a.stu_idcard, 
					  a.stu_birthday, d.pa_shortname, 
					  t_student_status.student_prop 
				FROM t_student a 
					 INNER JOIN t_class b ON a.cls_id = b.cls_id 
					 INNER JOIN t_subject c ON b.sbj_id = c.sbj_id  
					 INNER JOIN t_nation ON t_nation.nation_code = a.nation_code 
					 INNER JOIN t_political_affiliation d ON d.pa_code = a.pa_code 
					 INNER JOIN t_student_status ON t_student_status.stu_id = a.stu_id 
				WHERE 
					  b.sbj_id = :subjectId 
					  AND 
					  b.grade = :grade 
					  AND 
					  c.institute_id = :depId 
				ORDER BY b.cls_name, a.stu_id ";
				
		queryObj = new Query( datasource=application.dnsSlave ) ;
   		queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
		queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
		queryObj.addParam( name="depId", value=depId, cfsqltype="cf_sql_varchar" );
		
		rs_student= queryObj.execute( sql=sql ).getResult();
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat(rs_subject.sbj_name & "专业," & grade & "级-学生名单", "utf-8");
		
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew( rs_subject.sbj_short & "专业学生名单", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "专业,年级,班级,学号,姓名,性别,民族,身份证号,出生日期,政治面貌,在校,学籍,电子注册,辅修,双学位,出国留学,联合培养");
		
		row = 1;
		
		for (a=1; a LTE rs_student.recordCount; a++ ){
			row++;
			
			/* 构建学生信息 */
			
			/* 构建考生数据 */
			rowData = rs_student["sbj_name"][a]
						& "," & rs_student["grade"][a]
						& "," & rs_student["cls_name"][a]
						& "," & rs_student["stu_id"][a]
						& "," & rs_student["stu_name"][a];
			
			if ( rs_student["stu_sex"][a] eq "1" ) {
				rowData = rowData & ",男";
			}
			else {
				rowData = rowData & ",女";
			}
						
			rowData = rowData & "," & rs_student["nation_name"][a]
						& "," & rs_student["stu_idcard"][a]
						& "," & datetimeAdvice.formatDateString( rs_student["stu_birthday"][a], "YYYY/MM/DD" )
						& "," & rs_student["pa_shortname"][a] & ","
						& " , , , , , , ";
			
			/* 写入学生信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
			/* 学生属性 */
			propAdvice.parseProperty( rs_student["student_prop"][a] );
			
			if ( propAdvice.getInSchool() ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "是", row, 11);
			}
			else {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "否", row, 11);
			}
			
			if ( propAdvice.getRecognized() ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "有", row, 12);
			}
			else {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "无", row, 12);
			}
			
			if ( propAdvice.getRegister() ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "是", row, 13);
			}
			
			if ( propAdvice.getAssist() ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "是", row, 14);
			}
			
			if ( propAdvice.getSecondary() ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "是", row, 15);
			}
			
			if ( propAdvice.getOutCountry() ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "是", row, 16);
			}
			
			if ( propAdvice.getExchanged() ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "是", row, 17);
			}
			
			
						
		}
									
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);

	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />

</cfsilent>